package com.sg.hms.DaoImpl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.sg.hms.Dao.H_SearchApplicationDao;
import com.sg.hms.Model.Employee;
import com.sg.hms.Model.H_applicaion;
import com.sg.hms.Model.H_moneyapplication;
import com.sg.hms.Util.JDBCUtil;

import net.sf.json.JSONObject;

public class H_SearchApplicationDaoImpl implements H_SearchApplicationDao{

	@Override
	public int getapplicationcount(String state) {
		// TODO Auto-generated method stub
		int sum=0;
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("select count(aId) sum from application where state=?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1, state);
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
				sum=rs.getInt("sum");
			}
			pst.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sum;
	}
	
	@Override
	public int getapplicationcount(String d,String state) {
		// TODO Auto-generated method stub
		int sum=0;
		Connection con=JDBCUtil.getConnection();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date date=null;
		try {
			java.util.Date udate=sdf.parse(d);
			date=new Date(udate.getTime());
		} catch (ParseException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		StringBuffer sql=new StringBuffer("select count(aId) sum from application where state=? and creatDate=?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1, state);
			pst.setDate(2, date);
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
				sum=rs.getInt("sum");
			}
			pst.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sum;
	}
	
	@Override
	public List<H_applicaion> searchapplication(int start, int pagesize,String state) {
		// TODO Auto-generated method stub
		List<H_applicaion> list=new ArrayList<H_applicaion>();
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("select A.aId aId, B.elName elName,C.dName dName,A.aContent aContent,A.creatDate creatDate");
		sql.append(" from application A,employee B,department C");
		sql.append(" where A.elId=B.elId and B.dId=C.dId and A.state=? limit ?,?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1, state);
			pst.setInt(2, start);
			pst.setInt(3, pagesize);
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				H_applicaion a=new H_applicaion();
				a.setaId(rs.getInt("aId"));
				a.setElName(rs.getString("elName"));
				a.setdName(rs.getString("dName"));
				a.setState(state);
				a.setaContent(rs.getString("aContent"));
				Date date=rs.getDate("creatDate");
				java.util.Date d=new java.util.Date(date.getTime());
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				String strdate=sdf.format(d);
				a.setCreatDate(strdate);
				
				list.add(a);
			}
			pst.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<H_applicaion> searchapplication(int start, int pagesize, String d,String state) {
		// TODO Auto-generated method stub
		List<H_applicaion> list=new ArrayList<H_applicaion>();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date date=null;
		try {
			java.util.Date udate=sdf.parse(d);
			date=new Date(udate.getTime());
		} catch (ParseException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("select A.aId aId, B.elName elName,C.dName dName,A.aContent aContent,A.creatDate creatDate");
		sql.append(" from application A,employee B,department C");
		sql.append(" where A.elId=B.elId and B.dId=C.dId and A.state=? and A.creatDate=? limit ?,?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1, state);
			pst.setDate(2, date);
			pst.setInt(3, start);
			pst.setInt(4, pagesize);
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				H_applicaion a=new H_applicaion();
				a.setaId(rs.getInt("aId"));
				a.setElName(rs.getString("elName"));
				a.setdName(rs.getString("dName"));
				a.setState(state);
				a.setaContent(rs.getString("aContent"));
				a.setCreatDate(d);
				
				list.add(a);
			}
			pst.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int creatMoneyApplication(int elId, int aId, double money) {
		// TODO Auto-generated method stub
		int flag=0;
		Connection con=JDBCUtil.getConnection();
		//获得当前日期
		java.util.Date date=new java.util.Date();
		Date d=new Date(date.getTime());
		StringBuffer sql=new StringBuffer("insert into moneyapplication(elId,money,state,aId,createDate) values(?,?,?,?,?)");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setInt(1, elId);
			pst.setDouble(2, money);
			pst.setString(3, "已提交");
			pst.setInt(4, aId);
			pst.setDate(5, d);
			int i=pst.executeUpdate();
			if(i>0){
				this.updateApplication(aId, "已申请","继续等待");
				flag=1;
			}
			pst.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public int updateApplication(int aId, String state,String note) {
		// TODO Auto-generated method stub
		int flag=0;
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("update application set state=?,note=? where aId=?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1, state);
			pst.setString(2, note);
			pst.setInt(3, aId);
			if(pst.executeUpdate()>0){
				flag=1;
			}
			pst.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public List<H_moneyapplication> searchmoneyapplication(int start, int pagesize,String maState) {
		// TODO Auto-generated method stub
		List<H_moneyapplication> list=new ArrayList<H_moneyapplication>();
		Connection con=JDBCUtil.getConnection();
		
		if(maState.equals("所有")){
			StringBuffer sql=new StringBuffer("select A.maId maId,A.money money,C.elName elName,D.dName dName,B.aContent aContent,A.createDate createDate,A.state ");
			sql.append(" from moneyapplication A,application B,employee C,department D");
			sql.append(" where A.aId=B.aId and B.elId=C.elId and C.dId=D.dId  limit ?,?");
			try {
				PreparedStatement pst=con.prepareStatement(sql.toString());
				
				pst.setInt(1, start);
				pst.setInt(2, pagesize);
				ResultSet rs=pst.executeQuery();
				while(rs.next()){
					H_moneyapplication ma=new H_moneyapplication();
					ma.setMaId(rs.getInt("maId"));
					ma.setElName(rs.getString("elName"));
					ma.setdName(rs.getString("dName"));
					ma.setState(rs.getString("state"));
					ma.setaContent(rs.getString("aContent"));
					ma.setMoney(rs.getDouble("money"));
					Date date=rs.getDate("createDate");
					java.util.Date d=new java.util.Date(date.getTime());
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					String strdate=sdf.format(d);
					ma.setCreateDate(strdate);
					
					list.add(ma);
				}
				pst.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}else{
			StringBuffer sql=new StringBuffer("select A.maId maId,A.money money,C.elName elName,D.dName dName,B.aContent aContent,A.createDate createDate ");
			sql.append(" from moneyapplication A,application B,employee C,department D");
			sql.append(" where A.aId=B.aId and B.elId=C.elId and C.dId=D.dId and  A.state=? limit ?,?");
			try {
				PreparedStatement pst=con.prepareStatement(sql.toString());
				pst.setString(1, maState);
				pst.setInt(2, start);
				pst.setInt(3, pagesize);
				ResultSet rs=pst.executeQuery();
				while(rs.next()){
					H_moneyapplication ma=new H_moneyapplication();
					ma.setMaId(rs.getInt("maId"));
					ma.setElName(rs.getString("elName"));
					ma.setdName(rs.getString("dName"));
					ma.setState(maState);
					ma.setaContent(rs.getString("aContent"));
					ma.setMoney(rs.getDouble("money"));
					Date date=rs.getDate("createDate");
					java.util.Date d=new java.util.Date(date.getTime());
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					String strdate=sdf.format(d);
					ma.setCreateDate(strdate);
					
					list.add(ma);
				}
				pst.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		
		
		return list;
	}

	@Override
	public int getmoneyapplicationcount(String maState) {
		// TODO Auto-generated method stub
		int sum=0;
		Connection con=JDBCUtil.getConnection();
		if(maState.equals("所有")){
			StringBuffer sql=new StringBuffer("select count(maId) sum from moneyapplication");
			try {
				PreparedStatement pst=con.prepareStatement(sql.toString());
			
				ResultSet rs=pst.executeQuery();
				if(rs.next()){
					sum=rs.getInt("sum");
				}
				pst.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}else{
			StringBuffer sql=new StringBuffer("select count(maId) sum from moneyapplication where state=?");
			try {
				PreparedStatement pst=con.prepareStatement(sql.toString());
				pst.setString(1, maState);
				ResultSet rs=pst.executeQuery();
				if(rs.next()){
					sum=rs.getInt("sum");
				}
				pst.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return sum;
	}

	@Override
	public List<H_moneyapplication> searchmoneyapplication(int start, int pagesize,String madate,String maState) {
		// TODO Auto-generated method stub
		List<H_moneyapplication> list=new ArrayList<H_moneyapplication>();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date date=null;
		try {
			java.util.Date udate=sdf.parse(madate);
			date=new Date(udate.getTime());
		} catch (ParseException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection con=JDBCUtil.getConnection();
		if(maState.equals("所有")){
			StringBuffer sql=new StringBuffer("select A.maId maId,A.money money,C.elName elName,D.dName dName,B.aContent aContent,A.createDate createDate,A.state ");
			sql.append(" from moneyapplication A,application B,employee C,department D");
			sql.append(" where A.aId=B.aId and B.elId=C.elId and C.dId=D.dId and A.createDate=? limit ?,?");
			try {
				PreparedStatement pst=con.prepareStatement(sql.toString());
				
				pst.setDate(1, date);
				pst.setInt(2, start);
				pst.setInt(3, pagesize);
				ResultSet rs=pst.executeQuery();
				while(rs.next()){
					H_moneyapplication ma=new H_moneyapplication();
					ma.setMaId(rs.getInt("maId"));
					ma.setElName(rs.getString("elName"));
					ma.setdName(rs.getString("dName"));
					ma.setaContent(rs.getString("aContent"));
					ma.setState(rs.getString("state"));
					ma.setMoney(rs.getDouble("money"));
					date=rs.getDate("createDate");
					java.util.Date d=new java.util.Date(date.getTime());
					String strdate=sdf.format(d);
					ma.setCreateDate(strdate);
					
					list.add(ma);
				}
				pst.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}else{
			StringBuffer sql=new StringBuffer("select A.maId maId,A.money money,C.elName elName,D.dName dName,B.aContent aContent,A.createDate createDate ");
			sql.append(" from moneyapplication A,application B,employee C,department D");
			sql.append(" where A.aId=B.aId and B.elId=C.elId and C.dId=D.dId and  A.state=? and A.createDate=? limit ?,?");
			try {
				PreparedStatement pst=con.prepareStatement(sql.toString());
				pst.setString(1, maState);
				pst.setDate(2, date);
				pst.setInt(3, start);
				pst.setInt(4, pagesize);
				ResultSet rs=pst.executeQuery();
				while(rs.next()){
					H_moneyapplication ma=new H_moneyapplication();
					ma.setMaId(rs.getInt("maId"));
					ma.setElName(rs.getString("elName"));
					ma.setdName(rs.getString("dName"));
					ma.setaContent(rs.getString("aContent"));
					ma.setState(maState);
					ma.setMoney(rs.getDouble("money"));
					date=rs.getDate("createDate");
					java.util.Date d=new java.util.Date(date.getTime());
					String strdate=sdf.format(d);
					ma.setCreateDate(strdate);
					
					list.add(ma);
				}
				pst.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return list;
	}

	@Override
	public int getmoneyapplicationcount(String madate,String maState) {
		// TODO Auto-generated method stub
		int sum=0;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date date=null;
		try {
			java.util.Date udate=sdf.parse(madate);
			date=new Date(udate.getTime());
		} catch (ParseException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection con=JDBCUtil.getConnection();
		if(maState.equals("所有")){
			StringBuffer sql=new StringBuffer("select count(maId) sum from moneyapplication where createDate=?");
			try {
				PreparedStatement pst=con.prepareStatement(sql.toString());
				
				pst.setDate(1, date);
				ResultSet rs=pst.executeQuery();
				if(rs.next()){
					sum=rs.getInt("sum");
				}
				pst.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}else{
			StringBuffer sql=new StringBuffer("select count(maId) sum from moneyapplication where state=? and createDate=?");
			try {
				PreparedStatement pst=con.prepareStatement(sql.toString());
				pst.setString(1, maState);
				pst.setDate(2, date);
				ResultSet rs=pst.executeQuery();
				if(rs.next()){
					sum=rs.getInt("sum");
				}
				pst.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return sum;
	}
}
